from openpyxl import load_workbook
from openpyxl import Workbook
from tkinter import *
from tkinter.ttk import *
import os
import timeit
app=Tk()
app.title("Get CPK JSL")
app.geometry('300x220')
app.resizable(False, False)
app.config(bg='#00cc66')
s=Style()
s.configure(".", background='#00cc66')
def get_jsl(result):
    start_time = timeit.default_timer()
    final_result='Open("$data/JMP.xlsx");\n'
    s1='Graph Builder(\nSize( 800,600 ),\nShow Control Panel( 0 ),\nVariables( Y( :'
    s2='), Gropu X( :CAV ) ),\nElements(\nPoints( Y, Legend( 7 ), Jitter( "Random Normal" ) ),\nBox Plot(Y, Legend( 8 ), Jitter(  "Random Normal" ) )\n),\nSendToReport(\nDispatch(\n{},\n"'
    s3='",\nScaleBox,\n{Format( "Fixed Dec", 15, 2 ),Min('
    s4='), Max( '
    s5='), Inc( 0.01 ), Minor Ticks( 0 ),\nAdd Ref Line('
    s6=', "Dashed", "Black", "Nominal '
    s7='", 1 ),\n Add Ref Line('
    s8=', "Dashed", "Red", "USL '
    s9=', "Dashed", "Red", "LSL '
    s10='", 1 )}\n),\nDispatch( {}, "graph title", TextEditBox, {Set Text( "SPC-'
    s11='" )} )\n),\nsave picture("SPC-'
    s12='.png","png")\n);\n'
    s13='Data Table( "data" ) << Stack(\ncolumns('
    s14=':Name("'
    s15='"),'
    s16='),\nSource Label Column( "'
    s17='"),\nStacked Data Column( "'
    s18=' Data" ),\nOutput Table( "'
    s19='")\n);\nGraph Builder(\nSize( 800,600 ),\nShow Control Panel( 0 ),\nVariables(\nX( :'
    s20=' ),\nY( :'
    s21=' Data),Gropu X( :CAV )),\nElements(\nPoints(  X, Y, Legend( 18 ), Jitter( "Random Normal" ) ),\nBox Plot( X, Y, Legend( 19 ), Jitter( "Random Normal" ) )\n),\nSendToReport(\nDispatch(\n{},\n"'
    s23=' Data", \nScale Box, \n{ Format( \"Fixed Dec\", 15, 2 ), Min('
    file_path = "D:/CPK.xlsx"
    if os.path.exists(file_path):
        result.config(text="文件已找到，开始加载")
        wb = load_workbook(filename=file_path)
        ws = wb.active
        result.config(text="开始判断尺寸类型是否为多乘")
        for row in range(2, ws.max_row + 1):
            if "-" in ws.cell(row,1).value:
                ws.cell(row,5).value="M"
                ws.cell(row,7).value=ws.cell(row,1).value.split("-")[0]
            else:
                ws.cell(row,5).value="S"
        result.config(text="开始判断尺寸类型是否为GD&T")
        for row in range(2, ws.max_row + 1):
            cell_value = ws.cell(row, 4).value
            if not isinstance(cell_value, (float, int)):
                ws.cell(row, 6).value = "GD&T"
            else:
                ws.cell(row, 6).value = "Tolerance"
        unique_values = set()
        for row in range(2, ws.max_row + 1):
            cell_value = ws.cell(row, 7).value
            if isinstance(cell_value, str):
                unique_values.add(cell_value)
        unique_list = list(unique_values)
        wb.save(file_path)
        result.config(text="开始编写脚本")
        for row in range(2, ws.max_row + 1):
            if ws.cell(row, 5).value == "S":
                if ws.cell(row, 6).value != "GD&T":
                    name=str(ws.cell(row, 1).value)
                    normal=str(ws.cell(row, 2).value)
                    usl=str(round(ws.cell(row, 3).value,3))
                    lsl=str(round(ws.cell(row, 4).value,3))
                    min=str(ws.cell(row, 4).value-0.02)
                    max=str(ws.cell(row, 3).value+0.02)
                    S=s1+name+s2+name+s3+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s7+lsl+s9+lsl+s10+name+s11+name+s12
                    final_result=final_result+S
                else:
                    name=str(ws.cell(row, 1).value)
                    normal=str(ws.cell(row, 2).value)
                    usl=str(round(ws.cell(row, 3).value,3))
                    min='-0.005'
                    max=str(ws.cell(row, 3).value+0.02)
                    S=s1+name+s2+name+s3+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s10+name+s11+name+s12
                    final_result=final_result+S
        for multi in unique_list:
            ss=''
            row=2
            name=multi
            for rows in range(2, ws.max_row + 1):
                if ws.cell(rows, 7).value==multi:
                    ss=ss+s14+str(ws.cell(rows, 1).value)+s15
                    row=rows
            if ws.cell(row, 6).value != "GD&T":
                normal=str(ws.cell(row, 2).value)
                usl=str(round(ws.cell(row, 3).value,3))
                lsl=str(round(ws.cell(row, 4).value,3))
                min=str(ws.cell(row, 4).value-0.02)
                max=str(ws.cell(row, 3).value+0.02)
                S=s13+ss+s16+name+s17+name+s18+name+s19+name+s20+name+s21+name+s23+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s7+lsl+s9+lsl+s10+name+s11+name+s12
                final_result=final_result+S
            else:
                normal=str(ws.cell(row, 2).value)
                usl=str(round(ws.cell(row, 3).value,3))
                min='-0.005'
                max=str(ws.cell(row, 3).value+0.02)
                S=s13+ss+s16+name+s17+name+s18+name+s19+name+s20+name+s21+name+s23+min+s4+max+s5+normal+s6+normal+s7+usl+s8+usl+s10+name+s11+name+s12
                final_result=final_result+S
        f = open("d:/CPK.jsl", "w")
        f.write(final_result)
        f.close()
        end_time = timeit.default_timer()
        t="脚本已生成D盘CPK.jsl，耗时"+str(round(end_time - start_time,5))+"秒"
        result.config(text=t)
    else:
        result.config(text="文件未找到，请确认")
#程序提示
tip=Label(app, text="说明：\n此程序目前仅使用于多模穴的CPK数据JMP脚本生成\n其他脚本生成需求待后续开发\n请将准备好的数据放置于电脑D盘，命名为‘CPK.xlsx’\n具体格式要求如下：\n第一列放置尺寸名，如SPC A 写A，多乘的写A-1,A-2\n第二列写normal值，第三列为USL\n第四列为LSL，对于GD&T尺寸LSL应为空\n标准化应在工作中贯穿始终\n标准化应在工作中贯穿始终\n标准化应在工作中贯穿始终")
tip.place(x=10, y=0)
#最终结果显示
result=Label(app, text="")
result.place(x=10, y=200)
#程序执行按钮
btn=Button(app, text="Get CPK JSL", command=lambda: get_jsl(result))
btn.place(x=100, y=180)
app.mainloop()